package com.echo.boot.poi;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

public class WriteTest {
    static final String PRE_SQL = "INSERT INTO base_sys_org(ou_code,parent_ou_code,ou_name,ou_prop,ou_type,ou_level,child_ou_code,state) VALUES";
    static final String PRE_SQL_D = "INSERT INTO base_sys_org(ou_code,parent_ou_code,ou_name,ou_prop,ou_type,ou_level,child_ou_code,state,is_leaf) VALUES";
    static final String SYS_STATION = "update base_sys_station set ";
    static final String SYS_STORE = "update base_sys_store set ";
    static final double X_PI = Math.PI * 3000.0 / 180.0;


    public static void main(String[] args) throws Exception {
//        int i = 0;
////得到一个NumberFormat的实例
//        NumberFormat nf = NumberFormat.getInstance();
////设置是否使用分组
//        nf.setGroupingUsed(false);
////设置最大整数位数
//        nf.setMaximumIntegerDigits(3);
////设置最小整数位数
//        nf.setMinimumIntegerDigits(3);
////输出测试语句
//        for (int j = 1; j < 55; j++) {
//            System.out.println(nf.format(i+j));
//        }
        read();
    }


    static void read() throws Exception {
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop + "/33.xlsx";

        FileInputStream fileInputStream = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheet = workbook.getSheet("Sheet1");

        int lastRowIndex = sheet.getLastRowNum();
//        String preCode = "flag";
//        int num = 0;
//        int index = 2;
        for (int i = 1; i <= lastRowIndex; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            StringBuilder sb = new StringBuilder();
            sb.append(SYS_STORE);
            BigDecimal gd_lng = new BigDecimal(getValue(row.getCell(1)));
            BigDecimal gd_lat = new BigDecimal(getValue(row.getCell(2)));
            Map<String, BigDecimal> map = gps_bdps(gd_lng, gd_lat);
//            map.put("bd_lng", new BigDecimal(bd_lng));
//            map.put("db_lat", new BigDecimal(bd_lat));
            sb.append("posx='").append(map.get("bd_lng").setScale(8, BigDecimal.ROUND_HALF_UP)).append("',");
            sb.append("posy='").append(map.get("bd_lat").setScale(8, BigDecimal.ROUND_HALF_UP)).append("'");
            sb.append(" where stand_code='").append(getValue(row.getCell(0))).append("';");
            System.out.println(sb.toString());
        }


    }


    static String getValue(Cell cell) {

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case NUMERIC: // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
                    if (cellValue.contains("E")) {
                        cellValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字
                    }
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = cell.getStringCellValue();
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    static void outFile(String ouName, String parentOuCode, int totalRow) throws Exception {


        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop + "/网点sql.xlsx";
        File file = new File(filePath);
        OutputStream outputStream = new FileOutputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 工作表
        XSSFSheet sheet = workbook.createSheet("Sheet1");
        // 第一行
        XSSFRow row0 = sheet.createRow(0);
        // 单元格
        row0.createCell(0).setCellValue("机构编码");
        row0.createCell(1).setCellValue("父级编码");
        row0.createCell(2).setCellValue("机构名称");
        row0.createCell(3).setCellValue("机构性质");
        row0.createCell(4).setCellValue("组织机构类型");
        row0.createCell(5).setCellValue("机构层级");
        row0.createCell(6).setCellValue("子机构个数");
        row0.createCell(7).setCellValue("状态");
        row0.createCell(8).setCellValue("sql");

        //rowNum
        String ouProp = "18";
        String ouType = "2";
        String ouLevel = "4";
        String childOuCode = "2";
        String status = "1";

        for (int rowNum = 1; rowNum < totalRow; rowNum++) {
            String ouCode = parentOuCode + "00" + rowNum;
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum <= 8; cellNum++) {
                Cell cell = row.createCell(cellNum);
                switch (cellNum) {
                    case 0:
                        cell.setCellValue(ouCode);
                        break;
                    case 1:
                        cell.setCellValue(parentOuCode);
                        break;
                    case 2:
                        cell.setCellValue(ouName);
                        break;
                    case 3:
                        cell.setCellValue(ouProp);
                        break;
                    case 4:
                        cell.setCellValue(ouType);
                        break;
                    case 5:
                        cell.setCellValue(ouLevel);
                        break;
                    case 6:
                        cell.setCellValue(childOuCode);
                        break;
                    case 7:
                        cell.setCellValue(status);
                        break;
                    case 8:
                        StringBuilder sb = new StringBuilder();
                        sb.append("(");
                        sb.append("'").append(ouCode).append("'");
                        sb.append("'").append(parentOuCode).append("'");
                        sb.append("'").append(ouName).append("'");
                        sb.append("'").append(ouProp).append("'");
                        sb.append("'").append(ouType).append("'");
                        sb.append("'").append(ouLevel).append("'");
                        sb.append("'").append(childOuCode).append("'");
                        sb.append("'").append(status).append("'");
                        sb.append(")");
                        cell.setCellValue(PRE_SQL + sb.toString());
                        break;
                }
            }
        }
        System.out.println("done");
        workbook.write(outputStream);
        outputStream.close();

    }

    // 百度坐标转高德
    static void bdps_gps(BigDecimal bd_lng, BigDecimal bd_lat) {
        double x = bd_lng.subtract(new BigDecimal("0.0065")).doubleValue();
        double y = bd_lat.subtract(new BigDecimal("0.006")).doubleValue();
        double z = Math.sqrt(Math.pow(x, 2) + Math.pow(y, 2)) - (Math.sin(y * X_PI) * 0.00002);
        double theta = Math.atan2(y, x) - (Math.cos(x * X_PI) * 0.00003);
        double gd_lng = z * Math.cos(theta);
        double gd_lat = z * Math.sin(theta);
        System.out.println("gd_lng : " + gd_lng + ";gd_lat :" + gd_lat);
    }


    //高德坐标转百度
    static Map<String, BigDecimal> gps_bdps(BigDecimal gd_lng, BigDecimal gd_lat) {
        double x = gd_lng.doubleValue();
        double y = gd_lat.doubleValue();
        double z = Math.sqrt(Math.pow(x, 2) + Math.pow(y, 2)) + (Math.sin(y * X_PI) * 0.00002);
        double theta = Math.atan2(y, x) + (Math.cos(x * X_PI) * 0.00003);
        double bd_lng = z * Math.cos(theta) + 0.0065;
        double bd_lat = z * Math.sin(theta) + 0.006;
        HashMap<String, BigDecimal> map = new HashMap<>();
        map.put("bd_lng", new BigDecimal(bd_lng));
        map.put("bd_lat", new BigDecimal(bd_lat));
        return map;
    }

}


